package com.byron;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.CellValue;
import org.apache.poi.xssf.usermodel.*;
import org.junit.Test;

import java.io.FileInputStream;
import java.io.IOException;

/**
* 测试poi操作excel
*
* @author byron
* @since 2024/8/21 9:42
*/
public class ExcelReadTest {

    String path = System.getProperty("user.dir");


    /**
    * 测试读excel03-HSSFWorkbook
    *
    * @author byron
    * @since 2024/8/21 9:43
    */
    @Test
    public void testRead03() throws IOException {

        FileInputStream inputStream = new FileInputStream(path+"/观众统计表03.xls");

        //1、读取Excel 工作簿
        HSSFWorkbook workbook = new HSSFWorkbook(inputStream);

        HSSFSheet sheet0 = workbook.getSheetAt(0);
        HSSFRow row0 = sheet0.getRow(0);

        HSSFCell cell0 = row0.getCell(0);

        HSSFCell cell1 = row0.getCell(1);

        //读取值时一定注意读取值的类型
        System.out.println(cell0.getStringCellValue());
        System.out.println(cell1.getNumericCellValue());


        inputStream.close();
    }



    /**
     * 测试读excel07-XSSFWorkbook
     *
     * @author byron
     * @since 2024/8/21 9:43
     */
    @Test
    public void testRead07() throws IOException {

        FileInputStream inputStream = new FileInputStream(path+"/观众统计表07.xlsx");

        //1、读取Excel 工作簿
        XSSFWorkbook workbook = new XSSFWorkbook(inputStream);

        XSSFSheet sheet0 = workbook.getSheetAt(0);
        XSSFRow row0 = sheet0.getRow(0);

        XSSFCell cell0 = row0.getCell(0);

        XSSFCell cell1 = row0.getCell(1);

        //读取值时一定注意读取值的类型
        System.out.println(cell0.getStringCellValue());
        System.out.println(cell1.getNumericCellValue());


        inputStream.close();
    }


    /**
    * 测试读取不同的数据类型
    *
    * @author byron
    * @since 2024/12/24 9:15
    */
    @Test
    public void testReadType() throws IOException {

        FileInputStream inputStream = new FileInputStream(path+"/明细表.xlsx");

        //1、读取Excel 工作簿
        XSSFWorkbook workbook = new XSSFWorkbook(inputStream);

        XSSFSheet sheet0 = workbook.getSheetAt(0);

        XSSFRow row0 = sheet0.getRow(0);

        //获取某一行的列数
        int physicalNumberOfCells = row0.getPhysicalNumberOfCells();

        for (int p = 0; p < physicalNumberOfCells; p++) {
            XSSFCell cell = row0.getCell(p);

            int cellType = cell.getCellType();

            if (cell != null) {
                String value = cell.getStringCellValue();
                System.out.print(value+"|");
            }
        }

        //获取总行数
        int rowNum = sheet0.getPhysicalNumberOfRows();
        for (int i = 1; i < rowNum; i++) {
            XSSFRow row = sheet0.getRow(i);

            if (row != null) {
                //获取某一行的总列数
                int cellCount = row.getPhysicalNumberOfCells();

                for (int j = 0; j < cellCount; j++) {

                    XSSFCell cell = row.getCell(j);

                    if (cell != null) {
                        int cellType = cell.getCellType();
                        String stringCellValue = "";
                        switch (cellType) {
                            //字符串
                            case XSSFCell.CELL_TYPE_STRING:{
                                System.out.print("STRING：");
                                stringCellValue = cell.getStringCellValue();

                                break;

                            }
                            //布尔
                            case XSSFCell.CELL_TYPE_BOOLEAN:{
                                System.out.print("BOOLEAN：");
                                stringCellValue = String.valueOf(cell.getBooleanCellValue());

                                break;
                            }
                            //布尔
                            case XSSFCell.CELL_TYPE_BLANK:{
                                System.out.print("BLANK：");

                                break;
                            }
                            //数字（日期和普通数字）
                            case XSSFCell.CELL_TYPE_NUMERIC:{

                                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                                    System.out.print("date：");
                                    stringCellValue = String.valueOf(cell.getDateCellValue());
                                } else {
                                    System.out.print("number[转化成字符串输出]：");

                                    //不是日期防止数字过长
                                    cell.setCellType(XSSFCell.CELL_TYPE_STRING);
                                    stringCellValue = String.valueOf(cell.getStringCellValue());
                                }


                                break;
                            }
                            //数据类型错误
                            case XSSFCell.CELL_TYPE_ERROR:{
                                System.out.print("数据类型错误：");

                                break;
                            }
                        }
                        System.out.println(stringCellValue);
                    }
                }
            }

        }

        inputStream.close();

    }


/**
* 计算公式
*
* @author byron
* @since 2025/1/7 9:08
*/
@Test
    public void testFORMULA() throws IOException {

        FileInputStream inputStream = new FileInputStream(path + "/计算公式.xlsx");

        //1、读取Excel 工作簿
        XSSFWorkbook workbook = new XSSFWorkbook(inputStream);

        XSSFSheet sheet0 = workbook.getSheetAt(0);

        XSSFRow row4 = sheet0.getRow(4);

        XSSFCell cell = row4.getCell(0);

        XSSFFormulaEvaluator evaluator = new XSSFFormulaEvaluator(workbook);

        int cellType = cell.getCellType();
        switch (cellType){
            case  XSSFCell.CELL_TYPE_FORMULA:{

                String cellFormula = cell.getCellFormula();
                System.out.println(cellFormula);

                CellValue value = evaluator.evaluate(cell);
                System.out.println(value.formatAsString());
                break;
            }
        }
    }

}
